import pool from "@/lib/db";
import { TIP_TAP_DEFAULT } from '@/config/settings'

const defaultContent = JSON.stringify(TIP_TAP_DEFAULT);
const articleModule = {
  get: async (limit = 10, offset = 0, conn = pool) => {
    const sql = `SELECT * FROM articles ORDER BY created_at DESC LIMIT ? OFFSET ?`;
    const [rows] = await conn.query(sql, [limit, offset]);
    const totalSql = `SELECT COUNT(*) as total FROM articles`;
    const [[{ total }]] = await conn.query(totalSql);
    const activeTotalSql = `SELECT COUNT(*) as activeTotal FROM articles WHERE status = 0`;
    const [[{ activeTotal }]] = await conn.query(activeTotalSql);
    return { articles: rows, total: total, activeTotal:activeTotal  };
  },
  getAll: async (conn = pool) => {
    const sql = `SELECT * FROM articles ORDER BY created_at DESC`;
    const [rows] = await conn.query(sql);
    return rows;
  },
  getById: async (id, conn = pool) => {
    const sql = `SELECT * FROM articles WHERE id = ?`;
    const [rows] = await conn.query(sql, [id]);
    return rows[0];
  },
  getBySlug: async (slug, conn = pool) => {
    const sql = `SELECT * FROM articles WHERE slug = ?`;
    const [rows] = await conn.query(sql, [slug]);
    return rows[0];
  },
  getByBlogId: async (blog_id, limit, offset, conn = pool) => {
    const sql = `SELECT * FROM articles WHERE blog_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?`;
    const [rows] = await conn.query(sql, [blog_id, limit, offset]);
    const totalSql = `SELECT COUNT(*) as total FROM articles WHERE blog_id = ?`;
    const [[{ total }]] = await conn.query(totalSql, [blog_id]);
    const activeTotalSql = `SELECT COUNT(*) as activeTotal FROM articles WHERE blog_id = ? AND status = 0`;
    const [[{ activeTotal }]] = await conn.query(activeTotalSql, [blog_id]);
    return { articles: rows, total: total, activeTotal:activeTotal  };
  },
  getSlugs: async (conn = pool) => {
    const sql = `SELECT slug FROM articles`;
    const [rows] = await conn.query(sql);
    return rows.map(row => row.slug);
  },
  deleteById: async (id, conn = pool) => {
    const sql = `DELETE FROM articles WHERE id = ?`;
    const res = await conn.query(sql, [id]);
    return res;
  },
  updateById: async (id, { slug, title, description, content, template, status, cover, sort, meta_title, meta_description, blog_id }, conn = pool) => {
    const sql = `UPDATE articles SET slug = ?, title = ?, description = ?, content = ?, template = ?, status = ?, cover = ?, sort = ?, meta_title = ?, meta_description = ?, blog_id = ? WHERE id = ?`;
    const res = await conn.query(sql, [slug, title, description, content, template, status, cover, sort, meta_title, meta_description, blog_id, id]);
    return res;
  },
  create: async ({ slug = '', title, description = '', content = defaultContent, template = 'defult', status = 1, cover = '', sort = 0, meta_title = '', meta_description = '', blog_id = null }, conn = pool) => {
    const sql = `INSERT INTO articles (slug, title, description, content, template, status, cover, sort, meta_title, meta_description, blog_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const [res] = await conn.query(sql, [slug, title, description, content, template, status, cover, sort, meta_title, meta_description, blog_id]);
    return res;
  }
}

export default articleModule;